﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;	// MSSQL Interface

namespace GE_Database_Manager
{
	public class System1 : Database
	{
		private SqlConnection _connection;
		private Standardized_Wrapper _wrapper;
		private string _serverName, _configDatabaseName, _historicalDatabaseName, _malfunctionTagFilepath, _commentsFilePath, _key;
        int progressStep;

		/// <summary>
		/// Creates a new instance of a System1 database
		/// </summary>
		/// <param name="conn">Built connection to a server</param>
		/// <param name="serverName">Name/Network path of the Server</param>
		/// <param name="configDbName">Name of the Configuration database</param>
		/// <param name="historicalDbName">Name of the Historical database</param>
		/// <param name="malfunctionTagPath">Path to the Malfunction Tags text file</param>
		/// <param name="commentsPath">Path to the Segment IDs text file</param>
		public System1(SqlConnection conn, string serverName, string configDbName, string historicalDbName, string malfunctionTagPath, string commentsPath)
		{
			_connection = conn;
			_serverName = serverName;
			_configDatabaseName = configDbName;
			_historicalDatabaseName = historicalDbName;
			_malfunctionTagFilepath = malfunctionTagPath;
			_commentsFilePath = commentsPath;
			_key = _connection.ConnectionString + _configDatabaseName + _historicalDatabaseName + _malfunctionTagFilepath
				+ _commentsFilePath;
		}

		public System1()
		{
			_connection = new SqlConnection();
			_serverName = "";
			_configDatabaseName = "";
			_historicalDatabaseName = "";
			_malfunctionTagFilepath = "";
			_commentsFilePath = "";
			_key = _configDatabaseName + _historicalDatabaseName + _malfunctionTagFilepath
				+ _commentsFilePath;
		}

		private void OpenConnection()
		{
			_connection.Close();
			_connection.Open();
		}

		#region Getters and Setters
		public SqlConnection Connection
		{
			get { return _connection; }
			set { }
		}
        public override int ProgressStep
        {
            get { return progressStep; }
            set { progressStep = value; }
        }
		public string ServerName
		{
			get { return _serverName; }
			set { }
		}

		public string ConfigurationDatabaseName
		{
			get { return _configDatabaseName; }
			set { }
		}

		public string HistoricalDatabaseName
		{
			get { return _historicalDatabaseName; }
			set { }
		}

		public string MalfunctionTagFilepath
		{
			get { return _malfunctionTagFilepath; }
			set { }
		}

		public string CommentsFilePath
		{
			get { return _commentsFilePath; }
			set { }
		}

		public override string Key
		{
			get { return _key; }
			set { }
		}
		#endregion

		public override bool Process(Standardized_Wrapper wrapper)
		{
            // set the database name
            wrapper.CurrentDB = "SYSTEM 1";

			_wrapper = wrapper;

			// TODO: threading
			return ProcessMachines();	// Process
		}

		private bool ProcessMachines()
		{
			// Select list of machines
			List<Tuple<int, string>> machines = GetMachines();

			if (machines == null) return false;

			// For each Machine (gearbox)
			foreach (Tuple<int, string> machine in machines)
			{
				DateTime startDate = DateTime.MaxValue, endDate = DateTime.MinValue;

				int originalMachineId = machine.Item1;
				string tag = machine.Item2;
				string type = "gearbox";

				// Get serial Number, site_location, workorder, make
				string serialNumber = GetSerialNumber(originalMachineId);
				string make = GetMake(originalMachineId);
				string workorder = GetWorkOrder();
				string site = GetSite();

				// Insert, get a MachineId
				int newMachineId = _wrapper.InsertMachine(serialNumber, type, site, tag, workorder, make);
				if (newMachineId == -1) return false;

				// Process Stages
				int newStageId = ProcessStages(originalMachineId, newMachineId);
				if ( newStageId== -1) return false;

				// Process Sensors
				ProcessSensors(originalMachineId, newStageId, ref startDate, ref endDate);

				// Process machine condition
				ProcessMachineCondition(newMachineId, startDate, endDate);
			}
				

			return false;
		}

		private List<Tuple<int, string>> GetMachines()
		{
			List<Tuple<int, string>> machines = new List<Tuple<int, string>>();

			try
			{
				using (SqlCommand sqlCmnd = new SqlCommand())
				{
					sqlCmnd.CommandType = System.Data.CommandType.Text;
					sqlCmnd.Connection = _connection;

					sqlCmnd.CommandText = String.Format(
						"SELECT DISTINCT Initial.[segment_id] ,Initial.[name] " +
						"FROM [{0}].[dbo].[bnc_segment] AS Initial " +
						"INNER JOIN [{0}].[dbo].[bnc_segment_child] AS Child1 " +
						"ON Initial.[segment_id] = Child1.[segment_id] " +
						"INNER JOIN [{0}].[dbo].[bnc_segment_child] AS Sensor " +
						"ON Child1.[child_sg_id]=Sensor.[segment_id] " +
						"INNER JOIN [{0}].[dbo].[bnc_segment_child] AS Child3 " +
						"ON Sensor.[child_sg_id] = Child3.[segment_id] " +
						"JOIN [{0}].[dbo].[bnc_segment] as Seg1 " +
						"ON Seg1.[segment_id] = Sensor.[child_sg_id] " +
						"INNER JOIN [{1}].[dbo].[waveform] AS Waveform " +
						"ON Child3.[child_sg_id] = Waveform.[segment_id] " +
						"JOIN [{0}].[dbo].[bnc_segment] as Seg " +
						"ON seg.[segment_id] = Child3.[child_sg_id] " +
						"WHERE (Initial.sg_type_code = 8004 OR Initial.[sg_type_code] = 8141 OR Initial.[sg_type_code] = 8142) " +
						"AND Initial.[segment_site] = 1 " +
						"AND Seg1.[name] not like '%aveform%';"
						, _configDatabaseName, _historicalDatabaseName);

					OpenConnection();
					using (SqlDataReader rdr = sqlCmnd.ExecuteReader())
					{
						while (rdr.Read())
						{
							Console.WriteLine(((int)rdr["segment_id"]).ToString() + ", " + rdr["name"]);
							machines.Add(new Tuple<int, string>((int)rdr["segment_id"], (string)rdr["name"]));
						}
					}
					sqlCmnd.Connection.Close();
				}
			}
			catch (SqlException sqlEx)
			{
				return null;
			}

			return machines.Count==0 ? null : machines;
		}

		private string GetSerialNumber(int originalMachineId)
		{
			try
			{
				string serial = "";
				using (SqlCommand sqlCmnd = new SqlCommand())
				{
					sqlCmnd.CommandType = System.Data.CommandType.Text;
					sqlCmnd.Connection = _connection;

					sqlCmnd.CommandText = String.Format(
						"SELECT [value_string] " +
						"FROM [{0}].[dbo].[segment_property_type] AS A " +
						"LEFT JOIN [{0}].[dbo].[segment_property] AS C ON A.property_code = C.property_code " +
						"WHERE C.segment_id = {1} AND A.[property_code] = 109 AND A.[type] = 3 AND A.[category] = 2;"
						, _configDatabaseName, originalMachineId);

					OpenConnection();
					using (SqlDataReader rdr = sqlCmnd.ExecuteReader())
					{
						while (rdr.Read())
						{
							Console.WriteLine("Serial Number: " + rdr["value_string"]);
							serial = (string)rdr["value_string"];
						}
					}
					sqlCmnd.Connection.Close();
				}
				return serial;
			}
			catch (SqlException sqlEx)
			{
				return "";
			}
		}

		private string GetMake(int originalMachineId)
		{
			try
			{
				string make = "";

				using (SqlCommand sqlCmnd = new SqlCommand())
				{
					sqlCmnd.CommandType = System.Data.CommandType.Text;
					sqlCmnd.Connection = _connection;

					sqlCmnd.CommandText = String.Format(
						"SELECT [value_string] " +
						"FROM [{0}].[dbo].[segment_property_type] AS A " +
						"LEFT JOIN [{0}].[dbo].[segment_property] AS C ON A.property_code = C.property_code " +
						"WHERE C.segment_id = {1} AND A.[property_code] = 107 AND A.[type] = 3 AND A.[category] = 2;"
						, _configDatabaseName, originalMachineId);

					OpenConnection();
					using (SqlDataReader rdr = sqlCmnd.ExecuteReader())
					{
						while (rdr.Read())
						{
							Console.WriteLine("Make: " + rdr["value_string"]);
							make = (string)rdr["value_string"];
						}
					}
					sqlCmnd.Connection.Close();

				}
				return make;
			}
			catch (SqlException sqlEx)
			{
				return "";
			}
		}

		private string GetWorkOrder()
		{
			return "";
		}

		private string GetSite()
		{
			return "";
		}

		private int ProcessStages(int originalMachineId, int newMachineId)
		{
			// Select list of stages by originalMachineId
			List<int> stageProperties = GetStages(originalMachineId);

			if (stageProperties == null) return -1;

			List<int> toothCounts = new List<int>();

			// For each stage property
			foreach (int stageProp in stageProperties)
			{
				// Get tooth count, designation, fixed, etc
				try
				{
					using (SqlCommand sqlCmnd = new SqlCommand())
					{
						sqlCmnd.CommandType = System.Data.CommandType.Text;
						sqlCmnd.Connection = _connection;

						sqlCmnd.CommandText = String.Format(
							"SELECT [value_int1] " +
							"FROM [{0}].[dbo].[segment_property_type] AS A " +
							"LEFT JOIN [RiyadhFeb2010_PIB_1_C~Config_2010_03_03T14_46_55].[dbo].[segment_property] AS C ON A.property_code = C.property_code " +
							"WHERE C.segment_id = {1} AND A.[property_code] = 50 AND A.[type] = 1 AND A.[category] = 2; "
							, _configDatabaseName, stageProp);

						OpenConnection();
						int toothCount = -1;
						using (SqlDataReader rdr = sqlCmnd.ExecuteReader())
						{
							while (rdr.Read())
							{
								Console.WriteLine("Stage Tooth Count: " + ((int)rdr["value_int1"]).ToString());
								toothCount = ((int)rdr["value_int1"]);
							}
						}
						sqlCmnd.Connection.Close();

						if (toothCount != -1)
						{
							toothCounts.Add(toothCount);
						}
					}
				}
				catch (SqlException sqlEx)
				{
					return -1;
				}
			}

			// Insert a stage
			Designation desig = (toothCounts.Count == 2 ? Designation.Parallel : Designation.Planetary);
			Console.WriteLine("Stage Designation: " + desig.ToString());

			if (desig == Designation.Parallel)
			{
				return _wrapper.InsertStage(desig, newMachineId, null, toothCounts[0], toothCounts[1], null, null, null, null);
			}
			else
			{
				// Currently do not have enough information to implement a Planetary stage
				return -1;
			}
		}

		private List<int> GetStages(int segmentId)
		{
			List<int> stages = new List<int>();
			try
			{
				using (SqlCommand sqlCmnd = new SqlCommand())
				{
					sqlCmnd.CommandType = System.Data.CommandType.Text;
					sqlCmnd.Connection = _connection;

					sqlCmnd.CommandText = String.Format(
						"SELECT DISTINCT [child_sg_id] " +
						"FROM [{0}].[dbo].[bnc_segment_child] " +
						"WHERE [segment_id] = {1}"
						, _configDatabaseName, segmentId);

					OpenConnection();
					using (SqlDataReader rdr = sqlCmnd.ExecuteReader())
					{
						while (rdr.Read())
						{
							Console.WriteLine("Stage ID: " + ((int)rdr["child_sg_id"]).ToString());
							stages.Add((int)rdr["child_sg_id"]);
						}
					}
					sqlCmnd.Connection.Close();
				}
			}
			catch (SqlException sqlEx)
			{
				return null;
			}

			return stages.Count == 0 ? null : stages;
		}

		private bool ProcessSensors(int originalMachineId, int newStageId, ref DateTime startDate, ref DateTime endDate)
		{
			// Select list of sensors by machineId
			List<Tuple<int, string, string>> sensors = GetSensors(originalMachineId);

			// Get the StartDate and EndDate
			GetDateRange(ref sensors, ref startDate, ref endDate);

			// For each sensor
			foreach (Tuple<int, string, string> sensor in sensors)
			{
				// Insert sensor, get sensorId
				int newSensorId = _wrapper.InsertSensor(sensor.Item2 + " - " + sensor.Item3, newStageId);

				if (newSensorId == -1) return false;

				// Process Waveforms
				ProcessWaveforms(newSensorId, sensor.Item1);
			}

			return false;
		}

		private List<Tuple<int, string, string>> GetSensors(int originalMachineId)
		{
			List<Tuple<int, string, string>> sensors = new List<Tuple<int, string, string>>();
			try
			{
				using (SqlCommand sqlCmnd = new SqlCommand())
				{
					sqlCmnd.CommandType = System.Data.CommandType.Text;
					sqlCmnd.Connection = _connection;

					sqlCmnd.CommandText = String.Format(
						"SELECT DISTINCT Seg1.name, child3.[child_sg_id], Seg.name " +
						"FROM [{0}].[dbo].[bnc_segment] AS Initial " +
						"INNER JOIN [{0}].[dbo].[bnc_segment_child] AS Child1 ON Initial.[segment_id] = Child1.[segment_id] " +
						"INNER JOIN [{0}].[dbo].[bnc_segment_child] AS Sensor ON Child1.[child_sg_id]=Sensor.[segment_id] " +
						"INNER JOIN [{0}].[dbo].[bnc_segment_child] AS Child3 ON Sensor.[child_sg_id] = Child3.[segment_id] " +
						"JOIN [{0}].[dbo].[bnc_segment] as Seg1 ON Seg1.segment_id = Sensor.child_sg_id " +
						"INNER JOIN [{1}].[dbo].[waveform] AS Waveform ON Child3.child_sg_id = Waveform.segment_id " +
						"JOIN [{0}].[dbo].[bnc_segment] as Seg ON seg.segment_id = Child3.child_sg_id " +
						"WHERE Initial.[segment_id] = {2} AND Seg1.[name] NOT LIKE '%aveform';"
						, _configDatabaseName, _historicalDatabaseName, originalMachineId);

					OpenConnection();
					using (SqlDataReader rdr = sqlCmnd.ExecuteReader())
					{
						while (rdr.Read())
						{
							Console.WriteLine("Sensor Info: " + ((string)rdr[0]).ToString() + ((int)rdr[1]).ToString() + ((string)rdr[2]).ToString());

							sensors.Add(new Tuple<int, string, string>(((int)rdr[1]), ((string)rdr[0]), ((string)rdr[2])));
						}
					}
					sqlCmnd.Connection.Close();
				}
			}
			catch (SqlException sqlEx)
			{
				return null;
			}

			return sensors.Count == 0 ? null : sensors;
		}

		private void GetDateRange(ref List<Tuple<int, string, string>> sensors, ref DateTime startDate, ref DateTime endDate)
		{
			foreach(Tuple<int, string, string> sensor in sensors)
			{
				try
				{
					using (SqlCommand sqlCmnd = new SqlCommand())
					{
						sqlCmnd.CommandType = System.Data.CommandType.Text;
						sqlCmnd.Connection = _connection;

						sqlCmnd.CommandText = String.Format(
							"SELECT TOP 1 WF.[gmt] " +
							"FROM [{0}].[dbo].[waveform] AS WF " +
							"WHERE WF.[segment_id] = {1} ORDER BY WF.[gmt] ASC;"
							, _historicalDatabaseName, sensor.Item1);

						OpenConnection();
						using (SqlDataReader rdr = sqlCmnd.ExecuteReader())
						{
							while (rdr.Read())
							{
								DateTime date = (DateTime)rdr[0];

								if (date < startDate) startDate = date;
								if (date > endDate) endDate = date;
							}
						}
						sqlCmnd.Connection.Close();
					}

					using (SqlCommand sqlCmnd = new SqlCommand())
					{
						sqlCmnd.CommandType = System.Data.CommandType.Text;
						sqlCmnd.Connection = _connection;

						sqlCmnd.CommandText = String.Format(
							"SELECT TOP 1 WF.[gmt] " +
							"FROM [{0}].[dbo].[waveform] AS WF " +
							"WHERE WF.[segment_id] = {1} ORDER BY WF.[gmt] DESC;"
							, _historicalDatabaseName, sensor.Item1);

						OpenConnection();
						using (SqlDataReader rdr = sqlCmnd.ExecuteReader())
						{
							while (rdr.Read())
							{
								DateTime date = (DateTime)rdr[0];

								if (date < startDate) startDate = date;
								if (date > endDate) endDate = date;
							}
						}
						sqlCmnd.Connection.Close();
					}
				}
				catch (SqlException sqlEx)
				{
				}
			}
		}

		private int ProcessWaveforms(int sensorId, int originalSegmentId)
		{
			try
			{
				using (SqlCommand sqlCmnd = new SqlCommand())
				{
					sqlCmnd.CommandType = System.Data.CommandType.Text;
					sqlCmnd.Connection = _connection;

					sqlCmnd.CommandText = String.Format(
						"SELECT WF.[gmt], WF.[status], WF.[sample_rate], WF.[xdcr_scale_factor], WF.[mv_per_bit], WF.[bits_per_sample], WF.[start_rev_rpm], WF.[sample_data], UNITS.[abbrev] " +
						"FROM [{1}].[dbo].[waveform] AS WF " +
						"JOIN [{0}].[dbo].[waveform_setup] AS WFR ON WF.[segment_id] = WFR.[segment_id] " +
						"JOIN [{0}].[dbo].[bnc_eng_unit_type] AS UNITS ON WFR.[eu_type_code] = UNITS.[eu_type_code] " +
						"WHERE WF.[segment_id] = {2};"
						, _configDatabaseName, _historicalDatabaseName, originalSegmentId);

					OpenConnection();
					using (SqlDataReader rdr = sqlCmnd.ExecuteReader())
					{
						while (rdr.Read())
						{
							// Process Sample data into standard Engr Units
							// Standardized Sample = ( mv_per_bit / xdcr_scale_factor ) * sample
							Double mv_per_bit = Convert.ToDouble((Single)rdr[4]);
							Double xdcr_scale_factor = Convert.ToDouble((Single)rdr[3]);
							Byte[] samples = (Byte[])rdr[7];

							// Get the bits per sample
							Byte[] bits_per_sample = new byte[1];
							rdr.GetBytes(5, 0, bits_per_sample, 0, 1);
							int bitsPerSample = Convert.ToInt32(bits_per_sample[0]);
							if (bitsPerSample == 0 || bitsPerSample % 8 != 0) return -1;	// if bits per sample is 0 or not a multiple of 8
							int bytesPerSample = bitsPerSample / 8;
							if (samples.Length % bytesPerSample != 0) return -1;	// if number of samples does not line up with the expected # of bytes

							// Convert sample data to standardized value and store
							List<Byte> convertedSamples = new List<byte>();
							for (int i = 0; i < samples.Length; i += bytesPerSample)
							{
								Int32 sample;
								if (bytesPerSample == 2)
								{
									sample = BitConverter.ToInt16(samples, i);
								}
								else if (bytesPerSample == 4)
								{
									sample = BitConverter.ToInt32(samples, i);
								}
								else return -1;

								Single convertedSample  = Convert.ToSingle(sample * (mv_per_bit / xdcr_scale_factor));
								convertedSamples.AddRange(BitConverter.GetBytes(convertedSample));
							}

							// Time, RPM, Sample Rate, and Engr Unit Id
							DateTime gmt = (DateTime)rdr[0];
							int rpm = (int)rdr[6];
							int sampleRate = Convert.ToInt32(((Single)rdr[2]));
							int engrUnitId = _wrapper.GetEngrUnitIdFromAbbreviation((string)rdr[8]);

							// Status
							List<byte> status = new List<byte>();
							status.AddRange(BitConverter.GetBytes(Convert.ToUInt16(0)));	// Prepend two bytes of 0's, to maintain bit correspondence
							status.AddRange((byte[])rdr[1]);

							// Insert
							bool inserted = _wrapper.InsertWaveform(sensorId, gmt, rpm, sampleRate, 32, convertedSamples.ToArray(), engrUnitId, status.ToArray());
							// TODO: Logging
						}
					}
					sqlCmnd.Connection.Close();
				}
			}
			catch (SqlException sqlEx)
			{
				return -1;
			}

			return -1;	// error, no waveforms inserted
		}

		private bool ProcessMachineCondition(int machineId, DateTime start, DateTime end)
		{
			// Read in malfunctions
			String malfunctions = System.IO.File.ReadAllText(_malfunctionTagFilepath).Replace(System.Environment.NewLine, " ").Trim();
			if (String.IsNullOrEmpty(malfunctions)) malfunctions = "None";

			// Read in comments
			String comments = System.IO.File.ReadAllText(_commentsFilePath).Replace(System.Environment.NewLine, " ").Trim();
			if (String.IsNullOrEmpty(comments)) malfunctions = "None";

			// Insert machineCondition
			return _wrapper.InsertMachineCondition(machineId, start, end, malfunctions, comments, "System1");
		}
	}
}
